# -*- coding: utf-8 -*-
#Autor: Vitor Letras n.º 3947


from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.schema import Table, MetaData
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import NoSuchTableError
from sqlalchemy import func
from model import Instituicao, Grau, Curso, CursoInstituicao, Base
from xlrd import open_workbook
from codigos import *
import os
import operator

"""
Neste modulo é efectuada a leitura do ficheiro cna131fresultados.xls
e preenchida uma base de dados com o nome cna131fresultadosdb.
"""

class DbHandler:
	COD_INSTITUICAO = 0
	COD_CURSO = 1
	NOME_INSTITUICAO = 2
	NOME_CURSO = 3
	GRAU = 4
	VAGAS_INICIAIS = 5
	COLOCADOS = 6
	NOTA_ULTIMO = 7
	VAGAS_SOBRANTES = 8
	"""
	Declaração de constantes indicadores da posição dos items
	no ficheiro cna131fresultados
	"""
		
	def __init__(self):
		"""
		Abre uma ligação a base de dados
		Criação de um objecto que gere a base de dados
		Atraves do módulo model.py cria as tabelas
		"""
		engine = create_engine('sqlite:///cna131fresultadosdb.db', echo=False)
		Session = sessionmaker(bind=engine)
		self.session = Session()
		Base.metadata.create_all(engine)

	def populateDatabase(self):
		"""
        Nesta função é feita a leitura do ficheiro cna131fresultados.xls, com permissão de leitura
        é efectuado o preenchimento da base de dados, cna131fresultadosdb, sendo verificado
        se existem elementos duplicados, utilização de exceções.
		"""
		if(self.session.query(Curso).count() == 0):
			"""
			Verifica a existencia de dados na tabela curso.
			Caso não existam dados, vai preencher a base de dados.
			"""
			wb = open_workbook('cna131fresultados.xls', 'r')
			sheet = wb.sheet_by_index(0)

			for row in range(3, 1090): 
				cod_instituicao = sheet.cell(row, self.COD_INSTITUICAO).value
				cod_curso = sheet.cell(row, self.COD_CURSO).value
				nome_instituicao = sheet.cell(row, self.NOME_INSTITUICAO).value
				nome_curso = sheet.cell(row, self.NOME_CURSO).value
				nome_grau = sheet.cell(row,self.GRAU).value
				vagas_iniciais = str(sheet.cell(row, self.VAGAS_INICIAIS).value)
				colocados = str(sheet.cell(row, self.COLOCADOS).value)
				nota_ultimo = str(sheet.cell(row, self.NOTA_ULTIMO).value)
				vagas_sobrantes = str(sheet.cell(row, self.VAGAS_SOBRANTES).value)

				grau = Grau(nome_grau.upper())
				instituicao = Instituicao(cod_instituicao, nome_instituicao)
				curso = Curso(cod_curso, nome_curso, grau.cod_grau)

				try:
					existing = self.session.query(Grau).filter_by(nome_grau=grau.nome_grau).one()
					curso.cod_grau = existing.cod_grau
				except NoResultFound:
					self.session.add(grau)
					curso.cod_grau = self.session.query(Grau).filter_by(nome_grau=grau.nome_grau).one().cod_grau

				try:
					existing = self.session.query(Curso).filter_by(cod_curso=curso.cod_curso).one()
				except NoResultFound:
					self.session.add(curso)
            
				try:
					existing = self.session.query(Instituicao).filter_by(cod_instituicao=instituicao.cod_instituicao).one()
				except NoResultFound:
					self.session.add(instituicao)


				self.session.add(CursoInstituicao(instituicao.cod_instituicao, curso.cod_curso, vagas_iniciais, colocados, nota_ultimo, vagas_sobrantes))

			self.session.commit()
	
	def dropDatabase(self):
		os.remove('cna131fresultadosdb.db')
		
		
	def getColocadosInst(self):
		"""
			Neste metódo retorna uma lista com o numero de colocados por inttuicao
			Neste caso percorre se todos os registos da Tabela CursoInstituicao cujo campo cod_instituição
			seja igual ao cod_instituicao do objecto inst. Tendo uma variavel para contar o numero de colocados
			Criando uma lista com o nome da instituição e o numero de colocados.
		"""
		numColocados = 0
		listaNumColocados = []
		for instituicao in self.session.query(Instituicao):
			for alunosInstituicao in self.session.query(CursoInstituicao).filter_by(cod_instituicao = instituicao.cod_instituicao):
				numColocados += alunosInstituicao.colocados        
				listaNumColocados.append((instituicao.nome_instituicao.encode('utf-8'), numColocados))
				numColocados = 0
		listaNumColocados = sorted(listaNumColocados, reverse=True, key=operator.itemgetter((1)))
		return listaNumColocados

	def getColocadosDist(self):
		"""
		Neste metodo retorna uma lista com o numero de alunos colocados por distrito
		Neste metodo vai percorrer o Dicionario distrito e a tabela CursoInstiuicao onde o valor do value 
		for igual armazena o valor.
		"""
		numColocadosDistrito = 0
		numTotalColocados = 0.0
		lstColocadosDistrito = []
		for key, value in DISTRITOS.items():
			for alunosInstituicao in self.session.query(CursoInstituicao).filter(CursoInstituicao.cod_instituicao.in_(value)):
				numColocadosDistrito += alunosInstituicao.colocados
				numTotalColocados += alunosInstituicao.colocados
			lstColocadosDistrito.append((key, numColocadosDistrito))    
			numColocadosDistrito = 0
			lstColocadosDistrito = sorted(lstColocadosDistrito, reverse=True, key=operator.itemgetter((1)))
		return lstColocadosDistrito
		
	def getPercentagemAluColInst(self):
		"""
			Neste metódo retorna uma lista com o calculo da percentagem dos alunos.
			Neste caso percorre se todos os registos da Tabela CursoInstituicao cujo campo cod_instituição
			seja igual ao cod_instituicao do objecto inst. Tendo uma variavel para contar o numero de colocados
			Fazendo posteirormente o calcula da percentagem bom os valores acima calculados.
		"""
		numColocados = 0
		numTotalColocados=0.0
		listaNumColocados = []
		for instituicao in self.session.query(Instituicao):
			for alunosInstituicao in self.session.query(CursoInstituicao).filter_by(cod_instituicao = instituicao.cod_instituicao):
				numColocados += alunosInstituicao.colocados
				numTotalColocados += alunosInstituicao.colocados        
				listaNumColocados.append((instituicao.nome_instituicao.encode('utf-8'), numColocados))
				numColocados = 0
						
		calculoPercentagem = []
		for colInstituicao in listaNumColocados:
			calcPercentagem = (colInstituicao[1] * 100) / numTotalColocados
			calculoPercentagem.append((colInstituicao[0], round(calcPercentagem,2)))
			percentagem1 = sorted(calculoPercentagem, reverse=True, key=operator.itemgetter((1)))
		return percentagem1
		
	def getPermilagemAlunosDist(self):
		numColocadosDistrito = 0
		numTotalColocados = 0.0
		lstColocadosDistrito = []
		for key, value in DISTRITOS.items():
			for alunosInstituicao in self.session.query(CursoInstituicao).filter(CursoInstituicao.cod_instituicao.in_(value)):
				numColocadosDistrito += alunosInstituicao.colocados
				numTotalColocados += alunosInstituicao.colocados
			lstColocadosDistrito.append((key, numColocadosDistrito))    
			numColocadosDistrito = 0
			
		lstPermilagem = []
		for permilagem in lstColocadosDistrito:
			calculoPermilagem = (1000 * permilagem[1]) / numTotalColocados
			lstPermilagem.append((permilagem[0], round(calculoPermilagem, 3)))
			lstPermilagem = sorted(lstColocadosDistrito, reverse=True, key=operator.itemgetter((1)))
		return lstPermilagem
		
	def getVagasSobrantesInst(self):
		"""
			Neste metódo retorna uma lista com o numero de vagas sobrantes por distrito
			Neste caso percorre se todos os registos da Tabela CursoInstituicao cujo campo cod_instituição
			seja igual ao cod_instituicao do objecto inst. Tendo uma variavel para contar o numero de vagas sobrantes 
			por instituicao.
			Criando uma lista com o nome da instituição e o numero de colocados.
		"""
		numVagasSobrantes = 0
		lstVagasSobrantes = []
		for instituicao in self.session.query(Instituicao):
			for vagasInstituicao in self.session.query(CursoInstituicao).filter_by(cod_instituicao = instituicao.cod_instituicao):
				numVagasSobrantes += vagasInstituicao.vagas_sobrantes
			lstVagasSobrantes.append((instituicao.nome_instituicao.encode('utf-8'), numVagasSobrantes))
			numVagasSobrantes = 0
		lstVagasSobrantes = sorted(lstVagasSobrantes, reverse=True, key=operator.itemgetter((1)))
		return lstVagasSobrantes

	def getVagasSobrantesDist(self):
		"""
		Neste metodo retorna uma lista com o numero de vagas sobrantes por distrito
		Neste metodo vai percorrer o Dicionario distrito e a tabela CursoInstiuicao onde o valor do value 
		for igual armazena o valor.
		"""
		numVagasDistrito = 0
		vagasDistrito = []
		for key, value in DISTRITOS.items():
			for vagasInstituicao in self.session.query(CursoInstituicao).filter(CursoInstituicao.cod_instituicao.in_(value)):
				numVagasDistrito += vagasInstituicao.vagas_sobrantes
			vagasDistrito.append((key, numVagasDistrito))
			numVagasDistrito = 0
		vagasDistrito = sorted(vagasDistrito, reverse=True, key=operator.itemgetter((1)))
		return vagasDistrito
